using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.Odbc;

namespace OWED.Backend
{
    class DBConn
    {
        OdbcConnection dbc;

        public DBConn()
        {
            string connectionString = @"Driver={MySQL};SERVER=cs430.student.iastate.edu;DATABASE=cs430;USER=cs430;PASSWORD=cs430";
            dbc = new OdbcConnection(connectionString);
        }
        public bool Open()
        {
            try
            {
                dbc.Open();
                return true;
            }
            catch (System.Data.Odbc.OdbcException oe)
            {
                throw oe;
            }
        }
        public bool Close()
        {
            try
            {
                dbc.Close();
                return true;
            }
            catch (OdbcException oe)
            {
                throw oe;
            }
        }
        protected DataSet executeQuery(string table, string[] parameters, string condition)
        {
            try
            {
                string sqlString = "SELECT ";
                if (parameters == null)
                    sqlString += "*";
                else
                    sqlString += string.Join(",", parameters);
                sqlString += " FROM " + table;
                if (condition != "")
                    sqlString += " WHERE " + condition;
                OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
                DataSet ds = new DataSet("cs430");
                //MessageBox.Show(sqlString);
                dbDataAdapter.Fill(ds, table);
                return ds;
            }
            catch (OdbcException oe)
            {
                throw oe;
            }
        }
        protected bool executeInsert(string[] parameters, string[] values, string table)
        {
            try
            {
                string sqlString = "INSERT INTO " + table;
                sqlString += " (" + string.Join(",", parameters) + ")";
                sqlString += " VALUES ('" + string.Join("','", values) + "')";
                OdbcCommand dbCommand = new OdbcCommand(sqlString, dbc);
                //MessageBox.Show(sqlString);
                return (dbCommand.ExecuteNonQuery() > 0);
            }
            catch (OdbcException oe)
            {
                throw oe;
            }
        }
        protected bool executeDelete(string condition, string table)
        {
            try
            {
                string sqlString = "DELETE FROM " + table;
                sqlString += " WHERE" + condition;
                OdbcCommand dbCommand = new OdbcCommand(sqlString, dbc);
                //MessageBox.Show(sqlString);
                return (dbCommand.ExecuteNonQuery() > 0);
            }
            catch (OdbcException oe)
            {
                throw oe;
            }
        }
        public bool Login(string user, string password)
        {
            user.Replace("'", "\'");
            password.Replace("'", "\'");
            string table = "User";
            string cond = "Username='" + user + "' AND Password = '" + password + "'";
            return !isEmpty(executeQuery(table, null, cond));
        }
        public bool CreateUser(string user, string password)
        {
            user.Replace("'", "\'");
            password.Replace("'", "\'");
            string[] values = new string[2];
            string[] param = new string[2];
            param[0] = "Username";
            param[1] = "Password";
            values[0] = user;
            values[1] = password;
            return executeInsert(param, values, "User");
        }
        public int getPermission(string user, int docID)
        {
            user.Replace("'", "\'");
            string[] param = new string[1];
            param[0] = "Level";
            string cond = "Username='" + user + "' AND DocID='" + docID + "'";
            DataSet ds = executeQuery("Permission", param, cond);
            if (ds.Tables[0].Rows.Count > 0)
                return (int)ds.Tables[0].Rows[0].ItemArray[0];
            else
                return -1;
        }
        public int getPermission(string user, string docTitle)
        {
            user.Replace("'", "\'");
            docTitle.Replace("'", "\'");
            string[] param = new string[1];
            param[0] = "Level";
            DataSet ds = executeQuery("DocStorage", null, "WHERE Title='" + docTitle + "'");
            string docID = ds.Tables[0].Rows[0].ItemArray[0].ToString();
            string cond = "Username='" + user + "' AND DocID=" + docID;
            ds = executeQuery("Permission", param, cond);
            if (ds.Tables[0].Rows.Count > 0)
                return (int)ds.Tables[0].Rows[0].ItemArray[0];
            else
                return -1;
        }
        public bool setPermission(string user, int docID, int level)
        {
            user.Replace("'", "\'");
            string[] param = new string[3];
            param[0] = "Username";
            param[1] = "DocID";
            param[2] = "Level";
            string[] values = new string[3];
            values[0] = user;
            values[1] = docID.ToString();
            values[2] = level.ToString();
            return executeInsert(param, values, "Permission");
        }
        public bool setPermission(string user, string docTitle, int level)
        {
            user.Replace("'", "\'");
            docTitle.Replace("'", "\'");
            DataSet ds = executeQuery("DocStorage", null, "WHERE Title='" + docTitle + "'");
            string docID = ds.Tables[0].Rows[0].ItemArray[0].ToString();
            string[] param = new string[3];
            param[0] = "Username";
            param[1] = "DocID";
            param[2] = "Level";
            string[] values = new string[3];
            values[0] = user;
            values[1] = docID;
            values[2] = level.ToString();
            return executeInsert(param, values, "Permission");
        }
        public DataSet getAppliedProposals()
        {
            //TO-DO: grab the changeAtoms too
            //MessageBox.Show(sqlString);
            string sqlString = "SELECT Proposal.*,ChangeAtom.* FROM Proposal,ChangeAtom WHERE ";
            sqlString += "Proposal.ProposalID = ChangeAtom.ProposalID ";
            sqlString += "AND Proposal.Applied = 1";
            OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
            DataSet ds = new DataSet("cs430");
            dbDataAdapter.Fill(ds, "Proposals");
            return ds;
        }
        public DataSet getAppliedProposals(int docID)
        {
            //MessageBox.Show(sqlString);
            string sqlString = "SELECT Proposal.*,ChangeAtom.* FROM Proposal,ChangeAtom WHERE ";
            sqlString += "Proposal.ProposalID = ChangeAtom.ProposalID ";
            sqlString += "AND Proposal.DocID = " + docID + " ";
            sqlString += "AND Proposal.Applied = 1";
            OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
            DataSet ds = new DataSet("cs430");
            dbDataAdapter.Fill(ds, "Proposals");
            return ds;
        }
        public DataSet getProposals()
        {
            //MessageBox.Show(sqlString);
            string sqlString = "SELECT Proposal.*,ChangeAtom.* FROM Proposal,ChangeAtom WHERE ";
            sqlString += "Proposal.ProposalID = ChangeAtom.ProposalID ";
            OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
            DataSet ds = new DataSet("cs430");
            dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
            dbDataAdapter.Fill(ds, "Proposals");
            return ds;
        }
        public DataSet getProposals(int docID)
        {
            //MessageBox.Show(sqlString);
            string sqlString = "SELECT Proposal.*,ChangeAtom.* FROM Proposal,ChangeAtom WHERE ";
            sqlString += "Proposal.ProposalID = ChangeAtom.ProposalID ";
            sqlString += "AND Proposal.DocID = " + docID + " ";
            OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
            DataSet ds = new DataSet("cs430");
            dbDataAdapter.Fill(ds, "Proposals");
            return ds;
        }
        public DataSet getProposalMeta(string DocTitle, int docVersion)
        {
            string sqlString = "SELECT distinct Proposal.* FROM Proposal, DocStorage where Proposal.DocID = DocStorage.DocID ";
            sqlString += "and DocStorage.Title = '" + DocTitle + "' and Proposal.BaseVersion = " + docVersion;
            OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
            DataSet ds = new DataSet("cs430");
            dbDataAdapter.Fill(ds, "Proposals");
            return ds;
        }
        public DataSet getAppliedProposals(string docTitle)
        {
            docTitle.Replace("'", "\'");
            DataSet ds = executeQuery("DocStorage", null, "Title='" + docTitle + "'");
            if (!isEmpty(ds))
            {
                string docID = ds.Tables[0].Rows[0].ItemArray[0].ToString();
                string sqlString = "SELECT Proposal.*,ChangeAtom.* FROM Proposal,ChangeAtom WHERE ";
                sqlString += "Proposal.ProposalID = ChangeAtom.ProposalID ";
                sqlString += "AND Proposal.DocID = " + docID + " ";
                sqlString += "AND Proposal.Applied = 1";
                OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
                ds = new DataSet("cs430");
                dbDataAdapter.Fill(ds, "Proposals");
                return ds;
            }
            else
                return null;
        }
        public DataSet getProposals(string docTitle)
        {
            docTitle.Replace("'", "\'");
            DataSet ds = executeQuery("DocStorage", null, "Title='" + docTitle + "'");
            if (!isEmpty(ds))
            {
                string docID = ds.Tables[0].Rows[0].ItemArray[0].ToString();
                string sqlString = "SELECT Proposal.*,ChangeAtom.* ";
                sqlString += " FROM Proposal,ChangeAtom, ";
                sqlString += "(SELECT MAX(StorageVersion) as LatestVersion, DocID FROM DocStorage Where Title='" + docTitle + "' ";
                sqlString += "GROUP BY DocID) maxResults ";
                sqlString += "Where Proposal.ProposalID = ChangeAtom.ProposalID ";
                sqlString += "AND Proposal.DocID = " + docID + " and maxResults.DocID = Proposal.DocID ";
                OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
                ds = new DataSet("cs430");
                dbDataAdapter.Fill(ds, "Proposals");
                return ds;
            }
            else
                return null;
        }

        public DataSet getProposals(string docTitle, int version)
        {
            docTitle.Replace("'", "\'");
            DataSet ds = executeQuery("DocStorage", null, "Title='" + docTitle + "'");
            if (!isEmpty(ds))
            {
                string docID = ds.Tables[0].Rows[0].ItemArray[0].ToString();
                string sqlString = "SELECT Proposal.*,ChangeAtom.* FROM Proposal,ChangeAtom WHERE ";
                sqlString += "Proposal.ProposalID = ChangeAtom.ProposalID ";
                sqlString += "AND Proposal.DocID = " + docID + " and Proposal.BaseVersion="+version;
                OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
                ds = new DataSet("cs430");
                dbDataAdapter.Fill(ds, "Proposals");
                return ds;
            }
            else
                return null;
        }

        public DataSet getDiscussions()
        {
            //TODO : grab Comments too
            string sqlString = "SELECT Discussion.*, Comment.* from Discussion,Comment";
            sqlString += "WHERE Discussion.DiscussionID = Comment.DiscussionID";
            OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
            DataSet ds = new DataSet("cs430");
            //MessageBox.Show(sqlString);
            dbDataAdapter.Fill(ds, "Discussions");
            return ds;
        }
        public DataSet getDiscussions(int docID)
        {
            string sqlString = "SELECT Discussion.*, Comment.* from Discussion,Comment";
            sqlString += "WHERE Discussion.DiscussionID = Comment.DiscussionID";
            sqlString += "AND Discussion.DocID = " + docID;
            OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
            DataSet ds = new DataSet("cs430");
            //MessageBox.Show(sqlString);
            dbDataAdapter.Fill(ds, "Discussions");
            return ds;
        }
        public DataSet getDiscussions(string docTitle)
        {
            docTitle.Replace("'", "\'");
            DataSet ds = executeQuery("DocStorage", null, "WHERE Title='" + docTitle + "'");
            string docID = ds.Tables[0].Rows[0].ItemArray[0].ToString();
            string sqlString = "SELECT Discussion.*, Comment.* from Discussion,Comment";
            sqlString += "WHERE Discussion.DiscussionID = Comment.DiscussionID";
            sqlString += "AND Discussion.DocID = " + docID;
            OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
            ds = new DataSet("cs430");
            //MessageBox.Show(sqlString);
            dbDataAdapter.Fill(ds, "Discussions");
            return ds;
        }

        public bool createDiscussion(int docID, string tagLocation)
        {
            tagLocation.Replace("'", "\'");
            string[] param = new string[2];
            string[] value = new string[2];
            param[0] = "DocID";
            value[0] = docID.ToString();
            param[1] = "TagLocation";
            param[1] = tagLocation;
            return executeInsert(param, value, "Discussion");
        }

        public bool postComment(int discussionID, string username, string title, string text)
        {
            username.Replace("'", "\'");
            title.Replace("'", "\'");
            text.Replace("'", "\'");
            return postComment(discussionID, username, title, text, -1);
        }
        public bool postComment(int discussionID, string username, string title, string text, int parentCommentID)
        {
            username.Replace("'", "\'");
            title.Replace("'", "\'");
            text.Replace("'", "\'");
            string[] param;
            string[] value;
            if (parentCommentID > -1)
            {
                param = new string[5];
                value = new string[5];
                param[4] = "ParentCommentID";
                value[4] = parentCommentID.ToString();
            }
            else
            {
                param = new string[4];
                value = new string[4];
            }
            param[0] = "DiscussionID";
            value[0] = discussionID.ToString();
            param[1] = "Username";
            value[1] = username;
            param[2] = "Title";
            value[2] = title;
            param[3] = "Text";
            value[3] = text;
            return executeInsert(param, value, "Comment");
        }
        public DataSet getComment(int commentID)
        {
            return executeQuery("Comment", null, "CommentID=" + commentID);
        }
        public DataSet getCommentList()
        {
            return executeQuery("Comment", null, "");
        }
        public bool createDocument(string text, string title)
        {
            title.Replace("'", "\'");
            text.Replace("'", "\'");
            string[] param = new string[2];
            string[] value = new string[2];
            param[0] = "Content";
            value[0] = text;
            param[1] = "Title";
            value[1] = title;
            if (executeInsert(param, value, "InceptionDoc"))
            {
                param[0] = "DocID";
                DataSet ds = executeQuery("InceptionDoc", param, "Content='" + text + "'");
                string id = ds.Tables[0].Rows[0].ItemArray[0].ToString();
                param = new string[4];
                value = new string[4];
                param[0] = "Content";
                value[0] = text;
                param[1] = "DocID";
                value[1] = id;
                param[2] = "StorageVersion";
                value[2] = "0";
                param[3] = "Title";
                value[3] = title;
                return executeInsert(param, value, "DocStorage");
            }
            else
                return false;
        }
        public bool updateDocument(string text, string title)
        {
            title.Replace("'", "\'");
            text.Replace("'", "\'");
            DataSet ds = getDocument(title);
            string id = ds.Tables[0].Rows[0].ItemArray[0].ToString();
            string[] param = new string[4];
            string[] value = new string[4];
            param[0] = "Content";
            value[0] = text;
            param[1] = "DocID";
            value[1] = id;
            param[2] = "StorageVersion";
            value[2] = (int.Parse(ds.Tables[0].Rows[0].ItemArray[0].ToString()) + 1).ToString();
            param[3] = "Title";
            value[3] = title;
            return executeInsert(param, value, "DocStorage");
        }
        public bool deleteDocument(int docID)
        {
            return executeDelete("DocID=" + docID, "DocStorage");
        }
        public bool deleteDocument(string docTitle)
        {
            docTitle.Replace("'", "\'");
            return executeDelete("Title='" + docTitle + "'", "DocStorage");
        }

        /// <summary>
        /// Returns the document specified by the docID and version
        /// </summary>
        /// <param name="docID">Integer specifying the document id</param>
        /// <param name="version">integer specifying the document version</param>
        /// <returns>If the version of the document requested is currently stored
        /// in the database, a DataSet containting ONLY the table "Document" is 
        /// returned. This table contains the fields StorageVersion, DocID, Content,
        /// and Title in that order. 
        /// If the version of the document request is NOT currently stored in the 
        /// database, a DataSet containing the latest version of the document that 
        /// is earlier than the requested version and all of the proposals required
        /// to create the requested version is returned. The document to be edited
        /// is returned in the table "Document" and the proposals are contained in 
        /// the table "Proposals". The Proposals table contains the following fields
        /// in this order:
        /// Proposal.DocID, Proposal.ProposalID, Proposal.Applied, Proposal.Date,
        /// Proposal.Username, Proposal.BaseVersion, ChangeAtom.ProposalID,
        /// ChangeAtom.Content, ChangeAtom.ChangeOrder</returns>
        public DataSet getDocument(int docID, int version)
        {
            //TO-DO!!
            //if dataset is empty, return the document --------DONE
            //and changes needed to get the version you want --DONE WEEEEE!!
            DataSet ds = executeQuery("DocStorage", null, "StorageVersion=" + version + " AND DocID=" + docID);
            if (isEmpty(ds))
            {
                string sqlString = "SELECT latest.* FROM (SELECT MAX(StorageVersion) ";
                sqlString += "as StorageVersion,DocID,Content,Title FROM (SELECT * FROM DocStorage WHERE DocID=";
                sqlString += docID + " AND StorageVersion <= ";
                sqlString += version + ")older GROUP BY DocID) latest";
                try
                {
                    OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
                    ds = new DataSet("cs430");
                    //MessageBox.Show(sqlString);
                    dbDataAdapter.Fill(ds, "Document");
                    //get the version we got
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        string oldVersion = ds.Tables["Document"].Rows[0].ItemArray[0].ToString();
                        sqlString = "SELECT Proposal.*,ChangeAtom.* FROM Proposal,ChangeAtom WHERE ";
                        sqlString += "Proposal.BaseVersion < " + version + " ";
                        sqlString += "AND Proposal.BaseVersion >= " + oldVersion + " ";
                        sqlString += "AND Proposal.ProposalID = ChangeAtom.ProposalID ";
                        sqlString += "AND Proposal.DocID = " + docID + " ";
                        sqlString += "AND Proposal.Applied = 1";
                        dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
                        dbDataAdapter.Fill(ds, "Proposals");
                    }
                }
                catch (OdbcException oe)
                {
                    throw oe;
                }
            }
            return ds;
        }
        /// <summary>
        /// Get a document specified by the document title.
        /// </summary>
        /// <param name="title">Title of document to be returned</param>
        /// <param name="version">integer specifying the document version</param>
        /// <returns>If the version of the document requested is currently stored
        /// in the database, a DataSet containting ONLY the table "Document" is 
        /// returned. This table contains the fields StorageVersion, DocID, Content,
        /// and Title in that order. 
        /// If the version of the document request is NOT currently stored in the 
        /// database, a DataSet containing the latest version of the document that 
        /// is earlier than the requested version and all of the proposals required
        /// to create the requested version is returned. The document to be edited
        /// is returned in the table "Document" and the proposals are contained in 
        /// the table "Proposals". The Proposals table contains the following fields
        /// in this order:
        /// Proposal.DocID, Proposal.ProposalID, Proposal.Applied, Proposal.Date,
        /// Proposal.Username, Proposal.BaseVersion, ChangeAtom.ProposalID,
        /// ChangeAtom.Content, ChangeAtom.ChangeOrder</returns>
        public DataSet getDocument(string title, int version)
        {
            title.Replace("'", "\'");
            DataSet ds = executeQuery("DocStorage", null, "StorageVersion=" + version + " AND Title='" + title + "'");
            if (isEmpty(ds))
            {
                string sqlString = "SELECT latest.* FROM (SELECT MAX(StorageVersion) ";
                sqlString += "as StorageVersion,DocID,Content,Title FROM (SELECT * FROM DocStorage WHERE Title='" + title + "'";
                sqlString += " AND StorageVersion <= " + version + ")older GROUP BY DocID) latest";
                try
                {
                    OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
                    ds = new DataSet("cs430");
                    //MessageBox.Show(sqlString);
                    dbDataAdapter.Fill(ds, "Document");
                    //get the version we got
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        string oldVersion = ds.Tables["Document"].Rows[0].ItemArray[0].ToString();
                        string docID = ds.Tables["Document"].Rows[0].ItemArray[1].ToString();
                        sqlString = "SELECT Proposal.*,ChangeAtom.* FROM Proposal,ChangeAtom WHERE ";
                        sqlString += "Proposal.BaseVersion < " + version + " ";
                        sqlString += "AND Proposal.BaseVersion >= " + oldVersion + " ";
                        sqlString += "AND Proposal.ProposalID = ChangeAtom.ProposalID ";
                        sqlString += "AND Proposal.DocID = " + docID + " ";
                        sqlString += "AND Proposal.Applied = 1";
                        dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
                        dbDataAdapter.Fill(ds, "Proposals");
                    }
                }
                catch (OdbcException oe)
                {
                    throw oe;
                }
            }
            return ds;
        }
        /// <summary>
        /// Get the latest version of all of the documents in the database.
        /// </summary>
        /// <returns>A dataset containing the latest version of the 
        /// documents in the database</returns>
        public DataSet getDocumentList()
        {
            //get documents that are the lastest for their DocID
            string sqlString = "SELECT ds.* FROM DocStorage ds,(";
            sqlString += "SELECT MAX(StorageVersion) as LatestVersion, DocID FROM DocStorage GROUP BY DocID";
            sqlString += ") maxResults";
            sqlString += " WHERE ds.StorageVersion = maxResults.LatestVersion ";
            sqlString += " AND ds.DocID = maxResults.DocID";

            try
            {
                OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
                DataSet ds = new DataSet("cs430");
                //MessageBox.Show(sqlString);
                dbDataAdapter.Fill(ds, "ds");
                return ds;
            }
            catch (OdbcException oe)
            {
                throw oe;
            }
        }
        public DataSet getDocument(string docTitle)
        {
            docTitle.Replace("'", "\'");
            //get documents that are the lastest for their DocTitle
            string sqlString = "SELECT ds.*, maxResults.LatestVersion FROM DocStorage ds,(";
            sqlString += "SELECT MAX(StorageVersion) as LatestVersion, DocID FROM DocStorage GROUP BY DocID";
            sqlString += ") maxResults";
            sqlString += " WHERE ds.StorageVersion = maxResults.LatestVersion ";
            sqlString += " AND ds.DocID = maxResults.DocID";
            sqlString += " AND ds.Title = '" + docTitle + "'";

            try
            {
                OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
                DataSet ds = new DataSet("cs430");
                //MessageBox.Show(sqlString);
                dbDataAdapter.Fill(ds, "ds");
                return ds;
            }
            catch (OdbcException oe)
            {
                throw oe;
            }
        }
        public DataSet getDocument(int docID)
        {
            //get documents that are the lastest for their DocTitle
            string sqlString = "SELECT ds.* FROM DocStorage ds,(";
            sqlString += "SELECT MAX(StorageVersion) as LatestVersion, DocID FROM DocStorage GROUP BY DocID";
            sqlString += ") maxResults";
            sqlString += " WHERE ds.StorageVersion = maxResults.LatestVersion ";
            sqlString += " AND ds.DocID = maxResults.DocID";
            sqlString += " AND ds.DocID = " + docID;

            try
            {
                OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter(sqlString, dbc);
                DataSet ds = new DataSet("cs430");
                //MessageBox.Show(sqlString);
                dbDataAdapter.Fill(ds, "ds");
                return ds;
            }
            catch (OdbcException oe)
            {
                throw oe;
            }
        }

        public bool approveProposal(int proposalID)
        {
            try
            {
                string sqlString = "UPDATE Proposal SET Applied = 1,";
                sqlString += " ApplyDate = date(" + DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day + ")";
                sqlString += " WHERE ProposalID=" + proposalID;
                OdbcCommand dbCommand = new OdbcCommand(sqlString, dbc);
                return (dbCommand.ExecuteNonQuery() > 0);
            }
            catch (OdbcException oe)
            {
                throw oe;
            }
        }

        public DataSet getChangeAtoms(int ProposalID, string docTitle)
        { 
            docTitle.Replace("'", "\'");
            DataSet ds = getDocument(docTitle);
            string docID = ds.Tables[0].Rows[0].ItemArray[0].ToString();
            ds = executeQuery("ChangeAtom c, ProposalID p", null, "p.ProposalID=c.ProposalID AND p.ProposalID" + ProposalID + " AND p.DocID='" + docID + "'");
            return ds;
        }

        /// <summary>
        ///     Submits a proposal for the next version of a document.
        /// </summary>
        /// <param name="docTitle">identifies the document to associate the proposal with</param>
        /// <param name="username">records who made the proposal</param>
        /// <param name="changes">ChangeAtom serialized string</param>
        /// <returns></returns>
        public bool submitProposal(string docTitle, string username, List<string> changes)
        {
            // X 1. get docId from title.
            // X 2. get next available proposalID
            // X 3. insert into proposal
            // 4. insert into changeatom.
            docTitle.Replace("'", "\'");
            username.Replace("'", "\'");
            DataSet ds = getDocument(docTitle);
            bool success = false;
            String propID = "";
            string[] param;
            string[] values;
            if (!isEmpty(ds))
            {
                string docID = ds.Tables[0].Rows[0].ItemArray[0].ToString();
                param = new string[6];
                values = new string[6];
                param[0] = "DocID";
                values[0] = docID;
                param[1] = "Username";
                values[1] = username;
                param[2] = "Applied";
                values[2] = "0";
                param[3] = "BaseVersion";
                values[3] = (((int)ds.Tables[0].Rows[0]["LatestVersion"])+1).ToString();
                param[4] = "SubmitDate";
                DateTime dt = DateTime.Now;
                values[4] = dt.Year.ToString() + "-" + dt.Month.ToString() + "-" + dt.Day.ToString();
                param[5] = "ApplyDate";
                values[5] = null;
                success = executeInsert(param, values, "Proposal");
                
                param = new string[1];
                param[0] = "max(ProposalID)";
                if (success) propID = ((int)((executeQuery("Proposal", param, "")).Tables[0].Rows[0][0])).ToString();
            }
            else return false;
            param = new string[3];
            values = new string[3];
            for (int i = 0; success && i < changes.Count; i++)
            {
                param[0] = "ProposalID";
                values[0] = propID;
                param[1] = "Content";
                values[1] = changes[i];
                param[2] = "ChangeOrder";
                values[2] = null;
                try
                {
                    success &= executeInsert(param, values, "ChangeAtom");
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
            return success;
        }

        public DataSet ListVersions(String docTitle)
        { 
            // it's called StorageVersion in DocStorage
            docTitle.Replace("'", "\'");
            DataSet ds = getDocument(docTitle);
            if (!isEmpty(ds))
            {
                string docID = ds.Tables[0].Rows[0].ItemArray[0].ToString();
                string[] param = new string[1];
                param[0] = "StorageVersion";
                ds = executeQuery("DocStorage", param, "DocID = " + docID);
            }
            return ds;
        }

        private bool isEmpty(DataSet ds)
        {
            return (ds.Tables[0].Rows.Count <= 0);
        }
    }
}
